# 插入测试数据脚本
# 用于供应商管理功能的数据字典和供应商数据

param(
    [string]$ConnectionString = "Server=localhost;Database=ManufacturingERP;Trusted_Connection=true;TrustServerCertificate=true;",
    [string]$SqlFile = "insert-test-data.sql"
)

Write-Host "开始插入测试数据..." -ForegroundColor Green

# 检查SQL文件是否存在
if (-not (Test-Path $SqlFile)) {
    Write-Host "错误: SQL文件 '$SqlFile' 不存在!" -ForegroundColor Red
    exit 1
}

try {
    # 读取SQL文件内容
    $sqlContent = Get-Content $SqlFile -Raw -Encoding UTF8
    
    # 创建数据库连接
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $ConnectionString
    
    Write-Host "连接到数据库..." -ForegroundColor Yellow
    $connection.Open()
    
    # 创建命令对象
    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $connection
    $command.CommandText = $sqlContent
    $command.CommandTimeout = 300  # 5分钟超时
    
    Write-Host "执行SQL脚本..." -ForegroundColor Yellow
    $result = $command.ExecuteNonQuery()
    
    Write-Host "SQL脚本执行完成! 影响行数: $result" -ForegroundColor Green
    
    # 验证数据插入
    Write-Host "验证数据插入..." -ForegroundColor Yellow
    
    $verifyQueries = @(
        "SELECT 'BusinessPartnerLevels' as TableName, COUNT(*) as Count FROM BusinessPartnerLevels WHERE IsDeleted = 0",
        "SELECT 'BusinessPartnerCategories' as TableName, COUNT(*) as Count FROM BusinessPartnerCategories WHERE IsDeleted = 0",
        "SELECT 'BusinessNatures' as TableName, COUNT(*) as Count FROM BusinessNatures WHERE IsDeleted = 0",
        "SELECT 'Industries' as TableName, COUNT(*) as Count FROM Industries WHERE IsDeleted = 0",
        "SELECT 'Locations' as TableName, COUNT(*) as Count FROM Locations WHERE IsDeleted = 0",
        "SELECT 'People' as TableName, COUNT(*) as Count FROM People WHERE IsDeleted = 0",
        "SELECT 'Suppliers' as TableName, COUNT(*) as Count FROM Suppliers WHERE IsDeleted = 0"
    )
    
    foreach ($query in $verifyQueries) {
        $command.CommandText = $query
        $reader = $command.ExecuteReader()
        
        if ($reader.Read()) {
            $tableName = $reader["TableName"]
            $count = $reader["Count"]
            Write-Host "  $tableName`: $count 条记录" -ForegroundColor Cyan
        }
        
        $reader.Close()
    }
    
    Write-Host "数据验证完成!" -ForegroundColor Green
    
} catch {
    Write-Host "错误: $($_.Exception.Message)" -ForegroundColor Red
    Write-Host "详细错误信息: $($_.Exception)" -ForegroundColor Red
} finally {
    if ($connection -and $connection.State -eq 'Open') {
        $connection.Close()
        Write-Host "数据库连接已关闭" -ForegroundColor Yellow
    }
}

Write-Host "脚本执行完成!" -ForegroundColor Green 